// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.
suite("q88_spill") {
  sql """
    set enable_force_spill=true;
  """
  sql """
    set spill_min_revocable_mem=1;
  """
  sql """
    use regression_test_tpcds_sf1_unique_ck_p1;
  """
  qt_q88 """
SELECT *
FROM
  (
   SELECT count(*) h8_30_to_9
   FROM
     store_sales
   , household_demographics
   , time_dim
   , store
   WHERE (ss_sold_time_sk = time_dim.t_time_sk)
      AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
      AND (ss_store_sk = s_store_sk)
      AND (time_dim.t_hour = 8)
      AND (time_dim.t_minute >= 30)
      AND (((household_demographics.hd_dep_count = 4)
            AND (household_demographics.hd_vehicle_count <= (4 + 2)))
         OR ((household_demographics.hd_dep_count = 2)
            AND (household_demographics.hd_vehicle_count <= (2 + 2)))
         OR ((household_demographics.hd_dep_count = 0)
            AND (household_demographics.hd_vehicle_count <= (0 + 2))))
      AND (store.s_store_name = 'ese')
)  s1
, (
   SELECT count(*) h9_to_9_30
   FROM
     store_sales
   , household_demographics
   , time_dim
   , store
   WHERE (ss_sold_time_sk = time_dim.t_time_sk)
      AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
      AND (ss_store_sk = s_store_sk)
      AND (time_dim.t_hour = 9)
      AND (time_dim.t_minute < 30)
      AND (((household_demographics.hd_dep_count = 4)
            AND (household_demographics.hd_vehicle_count <= (4 + 2)))
         OR ((household_demographics.hd_dep_count = 2)
            AND (household_demographics.hd_vehicle_count <= (2 + 2)))
         OR ((household_demographics.hd_dep_count = 0)
            AND (household_demographics.hd_vehicle_count <= (0 + 2))))
      AND (store.s_store_name = 'ese')
)  s2
, (
   SELECT count(*) h9_30_to_10
   FROM
     store_sales
   , household_demographics
   , time_dim
   , store
   WHERE (ss_sold_time_sk = time_dim.t_time_sk)
      AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
      AND (ss_store_sk = s_store_sk)
      AND (time_dim.t_hour = 9)
      AND (time_dim.t_minute >= 30)
      AND (((household_demographics.hd_dep_count = 4)
            AND (household_demographics.hd_vehicle_count <= (4 + 2)))
         OR ((household_demographics.hd_dep_count = 2)
            AND (household_demographics.hd_vehicle_count <= (2 + 2)))
         OR ((household_demographics.hd_dep_count = 0)
            AND (household_demographics.hd_vehicle_count <= (0 + 2))))
      AND (store.s_store_name = 'ese')
)  s3
, (
   SELECT count(*) h10_to_10_30
   FROM
     store_sales
   , household_demographics
   , time_dim
   , store
   WHERE (ss_sold_time_sk = time_dim.t_time_sk)
      AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
      AND (ss_store_sk = s_store_sk)
      AND (time_dim.t_hour = 10)
      AND (time_dim.t_minute < 30)
      AND (((household_demographics.hd_dep_count = 4)
            AND (household_demographics.hd_vehicle_count <= (4 + 2)))
         OR ((household_demographics.hd_dep_count = 2)
            AND (household_demographics.hd_vehicle_count <= (2 + 2)))
         OR ((household_demographics.hd_dep_count = 0)
            AND (household_demographics.hd_vehicle_count <= (0 + 2))))
      AND (store.s_store_name = 'ese')
)  s4
, (
   SELECT count(*) h10_30_to_11
   FROM
     store_sales
   , household_demographics
   , time_dim
   , store
   WHERE (ss_sold_time_sk = time_dim.t_time_sk)
      AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
      AND (ss_store_sk = s_store_sk)
      AND (time_dim.t_hour = 10)
      AND (time_dim.t_minute >= 30)
      AND (((household_demographics.hd_dep_count = 4)
            AND (household_demographics.hd_vehicle_count <= (4 + 2)))
         OR ((household_demographics.hd_dep_count = 2)
            AND (household_demographics.hd_vehicle_count <= (2 + 2)))
         OR ((household_demographics.hd_dep_count = 0)
            AND (household_demographics.hd_vehicle_count <= (0 + 2))))
      AND (store.s_store_name = 'ese')
)  s5
, (
   SELECT count(*) h11_to_11_30
   FROM
     store_sales
   , household_demographics
   , time_dim
   , store
   WHERE (ss_sold_time_sk = time_dim.t_time_sk)
      AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
      AND (ss_store_sk = s_store_sk)
      AND (time_dim.t_hour = 11)
      AND (time_dim.t_minute < 30)
      AND (((household_demographics.hd_dep_count = 4)
            AND (household_demographics.hd_vehicle_count <= (4 + 2)))
         OR ((household_demographics.hd_dep_count = 2)
            AND (household_demographics.hd_vehicle_count <= (2 + 2)))
         OR ((household_demographics.hd_dep_count = 0)
            AND (household_demographics.hd_vehicle_count <= (0 + 2))))
      AND (store.s_store_name = 'ese')
)  s6
, (
   SELECT count(*) h11_30_to_12
   FROM
     store_sales
   , household_demographics
   , time_dim
   , store
   WHERE (ss_sold_time_sk = time_dim.t_time_sk)
      AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
      AND (ss_store_sk = s_store_sk)
      AND (time_dim.t_hour = 11)
      AND (time_dim.t_minute >= 30)
      AND (((household_demographics.hd_dep_count = 4)
            AND (household_demographics.hd_vehicle_count <= (4 + 2)))
         OR ((household_demographics.hd_dep_count = 2)
            AND (household_demographics.hd_vehicle_count <= (2 + 2)))
         OR ((household_demographics.hd_dep_count = 0)
            AND (household_demographics.hd_vehicle_count <= (0 + 2))))
      AND (store.s_store_name = 'ese')
)  s7
, (
   SELECT count(*) h12_to_12_30
   FROM
     store_sales
   , household_demographics
   , time_dim
   , store
   WHERE (ss_sold_time_sk = time_dim.t_time_sk)
      AND (ss_hdemo_sk = household_demographics.hd_demo_sk)
      AND (ss_store_sk = s_store_sk)
      AND (time_dim.t_hour = 12)
      AND (time_dim.t_minute < 30)
      AND (((household_demographics.hd_dep_count = 4)
            AND (household_demographics.hd_vehicle_count <= (4 + 2)))
         OR ((household_demographics.hd_dep_count = 2)
            AND (household_demographics.hd_vehicle_count <= (2 + 2)))
         OR ((household_demographics.hd_dep_count = 0)
            AND (household_demographics.hd_vehicle_count <= (0 + 2))))
      AND (store.s_store_name = 'ese')
)  s8
"""
}
